begin;
set search_path=_100351;
CREATE SEQUENCE _100351.mr_report_id_seq start 1;
CREATE SEQUENCE _100351.sys_report_cus_id_seq start 1;
CREATE SEQUENCE _100351.sys_master_filter_id_seq start 1;
CREATE TABLE master_report
(
    report_id integer DEFAULT nextval('_100351.mr_report_id_seq'),
    report_code character varying(10) NOT NULL,
    report_name character varying(255),
    module_id character(3),
    view_name character varying
);

ALTER TABLE ONLY master_report
    ADD CONSTRAINT master_report_pkey PRIMARY KEY (report_id);
ALTER TABLE ONLY master_report
    ADD CONSTRAINT mr_report_code_unq UNIQUE (report_code);

CREATE SEQUENCE _100351.mp_privilege_id_seq start 1;
CREATE TABLE master_privilege
(
    privilege_id integer DEFAULT nextval('_100351.mp_privilege_id_seq'),
    privilege_desc character varying
);

ALTER TABLE ONLY master_privilege
    ADD CONSTRAINT master_privilege_pkey PRIMARY KEY (privilege_id);

INSERT INTO master_privilege VALUES (1,'commercial');
INSERT INTO master_privilege VALUES (2,'financial');
INSERT INTO master_privilege VALUES (3,'product');
INSERT INTO master_privilege VALUES (4,'production');
INSERT INTO master_privilege VALUES (5,'shipment');
INSERT INTO master_privilege VALUES (6,'setup');
INSERT INTO master_privilege VALUES (7,'production_internal');

CREATE SEQUENCE _100351.master_column_map_column_id_seq start 1;
CREATE TABLE master_column_mapping (
    column_id integer DEFAULT nextval('_100351.master_column_map_column_id_seq') NOT NULL,
    script_variable_name character varying(40),
    db_column_name character varying,
    report_id integer,
    privilege_id integer,
    permission character(1) DEFAULT '-'::bpchar,
    fetch_column boolean DEFAULT true,
    CONSTRAINT mrc_permission_chk CHECK (permission = 'r' OR permission = 'e' OR permission = 'm' OR permission = '-')
);

ALTER TABLE ONLY master_column_mapping
    ADD CONSTRAINT master_column_mapping_pkey PRIMARY KEY (column_id);

ALTER TABLE ONLY master_column_mapping
    ADD CONSTRAINT mcm_report_script_variable_unq UNIQUE (report_id, script_variable_name);

ALTER TABLE ONLY master_column_mapping
    ADD CONSTRAINT mrc_privilege_id_fk FOREIGN KEY (privilege_id) REFERENCES master_privilege(privilege_id);

CREATE TABLE master_report_customization (
    column_id integer,
    display_name character varying(40),
    style character varying(255),
    display_summary boolean,
    decimal_places integer,
    format_details character varying(255),
    format_date character varying(20),
    display_order numeric
);


-- CREATE TABLE _100351report_customization (
--     column_id integer,
--     display_name character varying(40) NOT NULL,
--     style character varying(255),
--     display_summary boolean,
--     decimal_places integer,
--     format_details character varying(255),
--     format_date character varying(20),
--     display_order numeric
-- );




CREATE TABLE sys_report_customization (
    customization_id integer DEFAULT
nextval('_100351.sys_report_cus_id_seq'::text) NOT NULL,
    report_id integer NOT NULL,
    customization_name character varying NOT NULL,
    user_id character varying,
    privilege integer,
    permission character varying,
    is_active character(1) DEFAULT 'f'::bpchar,
    title character varying
);

CREATE TABLE report_customization (
    column_id integer,
    display_name character varying(40) NOT NULL,
    style character varying(255),
    display_summary boolean,
    decimal_places integer,
    format_details character varying(255),
    format_date character varying(20),
    display_order numeric,
    customization_id integer NOT NULL
);

ALTER TABLE ONLY report_customization
    ADD CONSTRAINT rc_column_id_fk FOREIGN KEY (column_id) REFERENCES
master_column_mapping(column_id);


CREATE TABLE sys_master_filter (
    filter_id integer DEFAULT nextval('_100351.sys_master_filter_id_seq'::text)
NOT NULL,
    filter_name character varying NOT NULL,
    filter_table_join character varying NOT NULL,
    form_field_name character varying[] NOT NULL,
    form_display character varying NOT NULL,
    fetch_condition character varying,
    order_by character varying
);

CREATE TABLE sys_filter_customization (
    customization_id integer NOT NULL,
    filter_id integer NOT NULL,
    filter_display_name character varying,
    filter_order integer,
    default_value character varying
);

alter table report_customization add column is_group char(1) default 'f';


commit;
